package cn.getech.data.development.utils;

import org.apache.commons.io.IOUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.hive.jdbc.HiveQueryResultSet;
import org.apache.poi.openxml4j.opc.internal.ZipHelper;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.*;
import org.joda.time.DateTime;

import java.io.*;
import java.sql.ResultSet;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;
import java.util.zip.ZipOutputStream;

public class BigDataExcelUtil {
    private static final String XML_ENCODING = "UTF-8";

    public static  void geneteBigData(List<String> column, ResultSet result,OutputStream outputStream) throws Exception {

        try (XSSFWorkbook wb = new XSSFWorkbook()) {
            XSSFSheet sheet = wb.createSheet("sheet1");
            Map<String, XSSFCellStyle> styles = createStyles(wb);
            String sheetRef = sheet.getPackagePart().getPartName().getName();
            try (FileOutputStream os = new FileOutputStream("./template.xlsx")) {
                wb.write(os);
            }
            substitute(new File("./template.xlsx"),  sheetRef.substring(1), outputStream,column,result,styles);
        }
    }
    public static  void geneteData(List<String> column, ResultSet result,OutputStream outputStream) throws Exception {
        if (result != null) {
            StringBuilder head=new StringBuilder();
            for (String s : column) {
                head.append(s).append(",");
            }
            head.append("\n");
            IOUtils.write(head.toString(),outputStream,XML_ENCODING);
            while(result.next() ){
                StringBuilder line=new StringBuilder();
                for (String s : column) {
                    line.append(result.getString(s)).append(",");
                }
                line.append("\n");
                IOUtils.write(line.toString(),outputStream,XML_ENCODING);
            }
        }else{
            outputStream.write(column.get(0).getBytes(XML_ENCODING));
        }
    }
    /**
     * Create a library of cell styles.
     */
    private static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb){
        Map<String, XSSFCellStyle> styles = new HashMap<>();
        XSSFDataFormat fmt = wb.createDataFormat();

        XSSFCellStyle style1 = wb.createCellStyle();
        style1.setAlignment(HorizontalAlignment.RIGHT);
        style1.setDataFormat(fmt.getFormat("0.0%"));
        styles.put("percent", style1);

        XSSFCellStyle style2 = wb.createCellStyle();
        style2.setAlignment(HorizontalAlignment.CENTER);
        style2.setDataFormat(fmt.getFormat("0.0X"));
        styles.put("coeff", style2);

        XSSFCellStyle style3 = wb.createCellStyle();
        style3.setAlignment(HorizontalAlignment.RIGHT);
        style3.setDataFormat(fmt.getFormat("$#,##0.00"));
        styles.put("currency", style3);

        XSSFCellStyle style4 = wb.createCellStyle();
        style4.setAlignment(HorizontalAlignment.RIGHT);
        style4.setDataFormat(fmt.getFormat("mmm dd"));
        styles.put("date", style4);

        XSSFCellStyle style5 = wb.createCellStyle();
        XSSFFont headerFont = wb.createFont();
        headerFont.setBold(true);
        style5.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style5.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style5.setFont(headerFont);
        styles.put("header", style5);

        return styles;
    }

    private static void generate(Writer out, Map<String, XSSFCellStyle> styles, List<String> titleNames, ResultSet resultSet) throws Exception {
        BufferedWriter bufferedWriter = new BufferedWriter(out,819200);
        SpreadsheetWriter sw = new SpreadsheetWriter(bufferedWriter);
        sw.beginSheet();

        if (titleNames!=null){
            //insert header row
            sw.insertRow(0);
            int styleIndex = styles.get("header").getIndex();
            for (int i=0;i<titleNames.size();i++){
                sw.createCell(i, titleNames.get(i), styleIndex);
            }
            sw.endRow();
        }


        //write data rows
        Integer num = 0;
        if (resultSet != null) {
            while(resultSet.next()&&num<=1048574){
                sw.insertRow(num+1);
                //表的行数据
                for (int i = 1; i < titleNames.size()+1; i++) {
                    if (null==resultSet.getString(i)){
                        sw.createCell(i-1, null);
                    }else{
                        sw.createCell(i-1, resultSet.getString(i));
                    }
                }
                sw.endRow();
                if (num%30000==0){
                    bufferedWriter.flush();
                }
                num++;
            }
        }
        sw.endSheet();
        bufferedWriter.flush();
        bufferedWriter.close();
    }

    /**
     *
     * @param zipfile the template file
     * @param entry the name of the sheet entry to substitute, e.g. xl/worksheets/sheet1.xml
     * @param out the stream to write the result to
     */
    private static void substitute(File zipfile, String entry, OutputStream out,List<String> column, ResultSet result,Map<String, XSSFCellStyle> styles) throws Exception {
        try (ZipFile zip = ZipHelper.openZipFile(zipfile)) {
            try (ZipOutputStream zos = new ZipOutputStream(out)) {
                Enumeration<? extends ZipEntry> en =  zip.entries();
                while (en.hasMoreElements()) {
                    ZipEntry ze =  en.nextElement();
                    if (!ze.getName().equals(entry)) {
                        zos.putNextEntry(new ZipEntry(ze.getName()));
                        try (InputStream is = zip.getInputStream(ze)) {
                            copyStream(is, zos);
                        }
                    }
                }
                zos.putNextEntry(new ZipEntry(entry));

                try (
                        Writer fw = new OutputStreamWriter(zos, XML_ENCODING)
                ) {
                    generate(fw, styles,column,result);
                }

            }

        }
    }

//    /**
//     *
//     * @param zipfile the template file
//     * @param tmpfile the XML file with the sheet data
//     * @param entry the name of the sheet entry to substitute, e.g. xl/worksheets/sheet1.xml
//     * @param out the stream to write the result to
//     */
//    private static void substituteSheet(File zipfile, String entry, OutputStream out) throws IOException {
//        try (ZipFile zip = ZipHelper.openZipFile(zipfile)) {
//            try (ZipOutputStream zos = new ZipOutputStream(out)) {
//                zos.putNextEntry(new ZipEntry(entry));
//                try (InputStream is = new FileInputStream(tmpfile)) {
//                    copyStream(is, zos);
//                    is.close();
//                    zos.close();
//                }
//            }
//            finally {
//                zip.close();
//            }
//        }
//    }

    private static void copyStream(InputStream in, OutputStream out) throws IOException {
        byte[] chunk = new byte[1024];
        int count;
        while ((count = in.read(chunk)) >=0 ) {
            out.write(chunk,0,count);
        }
    }

    /**
     * Writes spreadsheet data in a Writer.
     * (YK: in future it may evolve in a full-featured API for streaming data in Excel)
     */
    public static class SpreadsheetWriter {
        private final BufferedWriter _out;
        private int _rownum;

        SpreadsheetWriter(BufferedWriter out){
            _out = out;
        }

        void beginSheet() throws IOException {
            _out.write("<?xml version=\"1.0\" encoding=\""+XML_ENCODING+"\"?>" +
                    "<worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">" );
            _out.write("<sheetData>\n");

        }

        void endSheet() throws IOException {
            _out.write("</sheetData>");
            _out.write("</worksheet>");

        }

        /**
         * Insert a new row
         *
         * @param rownum 0-based row number
         */
        void insertRow(int rownum) throws IOException {
            _out.write("<row r=\""+(rownum+1)+"\">\n");
            this._rownum = rownum;
        }

        /**
         * Insert row end marker
         */
        void endRow() throws IOException {
            _out.write("</row>\n");
        }

        public void createCell(int columnIndex, String value, int styleIndex) throws IOException {
            String ref = new CellReference(_rownum, columnIndex).formatAsString();
            if(null!=value) {
                value = value.replace("&", "&amp;")
                        .replace("<", "&lt;")
                        .replace("'", "&apos;")
                        .replace("\"", "&quot;")
                        .replace(">", "&gt;");
            }
            _out.write("<c r=\""+ref+"\" t=\"inlineStr\"");
            if(styleIndex != -1) {
                _out.write(" s=\""+styleIndex+"\"");
            }
            _out.write(">");
            String changeData = StringUtils.isEmpty(value) ? "" : value;
            _out.write("<is><t>"+ changeData  +"</t></is>");
            _out.write("</c>");
        }

        public void createCell(int columnIndex, String value) throws IOException {
            createCell(columnIndex, value, -1);
        }

//        public void createCell(int columnIndex, double value, int styleIndex) throws IOException {
//            String ref = new CellReference(_rownum, columnIndex).formatAsString();
//            _out.write("<c r=\""+ref+"\" t=\"n\"");
//            if(styleIndex != -1) {
//                _out.write(" s=\""+styleIndex+"\"");
//            }
//            _out.write(">");
//            _out.write("<v>"+value+"</v>");
//            _out.write("</c>");
//        }

//        public void createCell(int columnIndex, double value) throws IOException {
//            createCell(columnIndex, value, -1);
//        }

//        public void createCell(int columnIndex, Calendar value, int styleIndex) throws IOException {
//            createCell(columnIndex, DateUtil.getExcelDate(value, false), styleIndex);
//        }
    }
}
